# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql
from twisted.enterprise import adbapi
import time
import datetime
from youtube_spider.items import VideosItem,ChannelsItem
import youtube_spider.common as common

class VideosPipeline(object):
    def open_spider(self, spider):
        db_params = dict(
            host=spider.settings['DB_HOST'],
            db=spider.settings['DB_NAME'],
            password=spider.settings['DB_PASSWORD'],
            user=spider.settings['DB_USER'],
            port=spider.settings['DB_PORT'],
            charset=spider.settings['DB_CHARESET'],
            cursorclass=pymysql.cursors.DictCursor)
        self.dbpool = adbapi.ConnectionPool('pymysql', cp_reconnect=True, **db_params)

    def process_item(self, item, spider):
        if isinstance(item, VideosItem):
            # 使用twiest将mysql插入变成异步
            query = self.dbpool.runInteraction(self.do_replace, item)
            # 处理错误 因为是异步的所以不能及时获得出错信息
            query.addErrback(self.handle_error)
        return item

    def handle_error(self, failure):
        print(failure)
        print('Failure')

    def do_replace(self, cursor, item):
        #created_at = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))  
        # 视频基础信息                    
        videos_sql = """
            replace into yt_videos(video_id, title,description,thumbnails,
            view_count,region_code,channel_id,published_at,collected_at) 
            values(%s, %s, %s, %s, %s,%s, %s, %s,%s)
        """
        item['published_at'] = common.utc2local(item['published_at'])
        cursor.execute(videos_sql,
                       (item['video_id'],item['title'],item['description'], item['thumbnails'],
                       item['view_count'],item['region_code'],item['channel_id'],
                       item['published_at'],item['collected_at']))
        # 关键字对应视频
        query_videos_sql = """
             replace into yt_query_videos(q, video_id) values(%s, %s)
        """
        cursor.execute(query_videos_sql,
                       (item['q'], item['video_id']))

    
class ChannelsPipeline(object):
    def open_spider(self, spider):
        db_params = dict(
            host=spider.settings['DB_HOST'],
            db=spider.settings['DB_NAME'],
            password=spider.settings['DB_PASSWORD'],
            user=spider.settings['DB_USER'],
            port=spider.settings['DB_PORT'],
            charset=spider.settings['DB_CHARESET'],
            cursorclass=pymysql.cursors.DictCursor)
        self.dbpool = adbapi.ConnectionPool('pymysql', cp_reconnect=True, **db_params)

    def process_item(self, item, spider):
        if isinstance(item, ChannelsItem):
            # 使用twiest将mysql插入变成异步
            query = self.dbpool.runInteraction(self.do_replace, item)
            # 处理错误 因为是异步的所以不能及时获得出错信息
            query.addErrback(self.handle_error)
        return item

    def do_replace(self, cursor, item):
        channels_sql = """
            replace into yt_channels(channel_id, title,thumbnails,
            view_count,subscriber_count,video_count,published_at,collected_at) 
            values(%s, %s, %s, %s, %s,%s, %s, %s)
        """
        item['published_at'] = common.utc2local(item['published_at'])
        cursor.execute(
            channels_sql, (item['channel_id'], item['title'], item['thumbnails'], item['view_count'],
                  item['subscriber_count'], item['video_count'],item['published_at'], 
                  item['collected_at']))

    def handle_error(self, failure):
        print(failure)
        print('Failure')


